﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
using System.Xml;
using System.IO;
namespace DeloUtils.Migra
{
    /// <summary>
    ///Проверка корректности данных
    ///</summary>
    class Check
    {
        public static event ProgressHandler CurrentRows;
        public static LocalDBCompact localdb = null;
        public static Int32 CountFiles = 0;
        public static Int32 ErrorFiles = 0;
        public static System.Windows.Forms.ListView lsvCheckError;

        public static void Import12mss(String Folder, OleDbConnection connection, EApi.Head ApiConnection)
        {
            System.Windows.Forms.ListViewItem item;
            string группа_документов = "";
            string группа_документов_due = "";
            string группа_документов_due_sync = "";
            string рег_номер = "";
            string рег_дата = "";
            string гражданин_корреспондент_имя = "";
            string гражданин_корреспондент_адрес = "";
            string дата_резолюции = "";
            string текст_резолюции = "";
            string автор_резолюции = "";
            string дата_резолюции_плановая = "";
            string дата_резолюции_фактическая = "";
            string ответсвенный_исполнитель = "";
            string исполнители = "";

            string Error = "";
            XmlReader resol, reply;
            ErrorFiles = 0;
            lsvCheckError.Items.Clear();
            List<string> files = new List<string>(System.IO.Directory.EnumerateFiles(Folder, "*.xml"));
            CountFiles = files.Count;
            //lsvCheckError.BeginUpdate();
            for (int progress = 0; progress < files.Count; progress++)
            {
                //Uri _file = new Uri(files[progress]);
                System.IO.FileInfo fi = new System.IO.FileInfo(files[progress]);
                XmlTextReader doc = new XmlTextReader(files[progress]);
                System.Windows.Forms.Application.DoEvents();
                CurrentRows(progress);
                try
                {
                    doc.ReadToFollowing("DOCGROUP");
                    doc.MoveToContent();
                    группа_документов_due = doc.GetAttribute("DUE");
                    группа_документов_due_sync = localdb.getDueDocgroup(группа_документов_due);
                    doc.ReadToDescendant("CLASSIF_NAME");
                    группа_документов = doc.ReadString();
                    doc.ReadToFollowing("FREE_NUM");
                    рег_номер = doc.ReadString();
                    doc.ReadToFollowing("DOC_DATE");
                    рег_дата = doc.ReadString();

                    doc.ReadToFollowing("RESOLUTIONS");
                    while (doc.ReadToDescendant("RESOLUTION"))
                    {
                        resol = doc.ReadSubtree();

                        while (resol.Read())
                        {
                            if (resol.NodeType == XmlNodeType.Element)
                            {
                                switch (resol.Name.ToUpper())
                                {
                                    case "DUE":
                                        resol.ReadToDescendant("CLASSIF_NAME");
                                        автор_резолюции = resol.ReadString();
                                        break;
                                    case "RESOLUTION_TEXT":
                                        текст_резолюции = resol.ReadString();
                                        break;
                                    case "RESOLUTION_DATE":
                                        дата_резолюции = resol.ReadString();
                                        break;
                                    case "PLAN_DATE":
                                        дата_резолюции_плановая = resol.ReadString();
                                        break;
                                    case "FACT_DATE":
                                        дата_резолюции_фактическая = resol.ReadString();
                                        break;
                                    case "REPLYS":
                                        ответсвенный_исполнитель = "";
                                        исполнители = "";
                                        while (resol.ReadToFollowing("REPLY"))
                                        {
                                            reply = resol.ReadSubtree();
                                            if (reply.ReadToDescendant("DUE"))
                                            {
                                                if (reply.ReadToDescendant("SURNAME"))
                                                {
                                                    if (исполнители == "")
                                                    {
                                                        исполнители = reply.ReadString();
                                                    }
                                                    else
                                                    {
                                                        исполнители += ";" + reply.ReadString();
                                                    }
                                                }
                                            }
                                            if (reply.ReadToFollowing("MAIN_FLAG"))
                                            {
                                                if (reply.ReadString() == "1")
                                                {
                                                    ответсвенный_исполнитель = исполнители.Split(';').Last<string>();
                                                }
                                            }
                                        }

                                        break;
                                }

                            }
                        }

                        if ((исполнители != "") && (ответсвенный_исполнитель != ""))
                            CorrectResolution(Folder, fi.Name, connection, рег_дата, рег_номер, группа_документов_due_sync,
                                дата_резолюции, текст_резолюции, автор_резолюции, дата_резолюции_плановая,
                                дата_резолюции_фактическая, ответсвенный_исполнитель, исполнители);
                    }


                    //if (doc.ReadToFollowing("REF_LETTERS"))
                    //{
                    //    if (doc.ReadToDescendant("REF_LETTER"))
                    //    {
                    //        doc.ReadToDescendant("CITIZEN_SURNAME");
                    //        гражданин_корреспондент_имя = doc.ReadString();
                    //        doc.ReadToDescendant("CITIZEN_ADDR");
                    //        гражданин_корреспондент_адрес = doc.ReadString();
                    //    }
                    //}
                    ////Error = CheckDocumentApi(ApiConnection, рег_дата, рег_номер, группа_документов_due_sync);
                    //Error = CheckDocumentDb(connection, fi.Name,
                    //    рег_дата, рег_номер, группа_документов_due_sync,
                    //    гражданин_корреспондент_имя, гражданин_корреспондент_адрес);
                    //if (Error != "")
                    //{
                    //    item = lsvCheckError.Items.Add(files[progress]);
                    //    item.SubItems.Add(группа_документов);
                    //    item.SubItems.Add(рег_номер);
                    //    item.SubItems.Add(рег_дата);
                    //    item.SubItems.Add(Error);
                    //    System.IO.File.AppendAllText(Folder + "\\Check.log", files[progress] + " \n\r" + Error + " \n\r");
                    //    ErrorFiles++;
                    //}

                }
                catch (Exception ex)
                {
                    System.IO.File.AppendAllText(Folder + "\\Check.log", files[progress] + " \n\r" + ex.Message + " \n\r");
                    item = lsvCheckError.Items.Add(files[progress]);
                    item.SubItems.Add(группа_документов);
                    item.SubItems.Add(рег_номер);
                    item.SubItems.Add(рег_дата);
                    item.SubItems.Add(ex.Message);
                    ErrorFiles++;
                }
                группа_документов_due = "";
                группа_документов = "";
                группа_документов_due_sync = "";
                рег_номер = "";
                рег_дата = "";
                гражданин_корреспондент_имя = "";
                гражданин_корреспондент_адрес = "";
                дата_резолюции = "";
                текст_резолюции = "";
                автор_резолюции = "";
                дата_резолюции_плановая = "";
                дата_резолюции_фактическая = "";
                ответсвенный_исполнитель = "";
                исполнители = "";
            }
            //lsvCheckError.EndUpdate();
            //localdb.Connection.Close();
        }

        //protected static string CheckDocumentApi(EApi.Head ApiConnection, string рег_дата, string рег_номер, string группа_документов_due_sync)
        //{
        //    EApi.ResultSet rs = ApiConnection.GetResultSet();
        //    rs.Clear();
        //    rs.Source = ApiConnection.GetCriterion("Table");
        //    ((EApi.SearchTables)(rs.Source)).SetParameters("Result", "Doc");
        //    ((EApi.SearchTables)(rs.Source)).SetParameters("Rc.DocYear", рег_дата.Split('-')[0]);
        //    ((EApi.SearchTables)(rs.Source)).SetParameters("Rc.RegNum", рег_номер);
        //    ((EApi.SearchTables)(rs.Source)).SetParameters("Rc.DocGroup", группа_документов_due_sync);

        //    rs.Fill();
        //    if (ApiConnection.ErrCode!=0) return ApiConnection.ErrText;
        //    if (rs.ItemCnt == 0) return "Не найдено";
        //    if (rs.ItemCnt > 1) return "Слишком много: " + rs.ItemCnt.ToString();
        //    return "";
        //}

        protected static string CheckDocumentDb(OleDbConnection connection, string file,
            string рег_дата, string рег_номер, string группа_документов_due_sync,
            string гражданин_корреспондент_имя, string гражданин_корреспондент_адрес)
        {
            DateTime doc_date = DateTime.Parse(рег_дата);
            string sql = "SELECT COUNT(*) FROM " + localdb.sqlprefix + "DOC_RC WHERE (DOC_YEAR=" +
                doc_date.Year.ToString() + ") AND (FREE_NUM_SEARCH='" +
                рег_номер.ToUpper() + "') AND (DUE_DOCGROUP='" + группа_документов_due_sync + "')";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            object result = cmd.ExecuteScalar();
            cmd.Dispose();
            if ((result != null) && (result != DBNull.Value))
            {
                if (result.ToString() == "0")
                {
                    try
                    {
                        Int32 isn = localdb.getImportIsnRc(Int32.Parse(file.Split('.')[0]));
                        return "Не верный ISN: " + isn.ToString();
                    }
                    catch { }
                    return "Не найдено";
                }
                if (result.ToString() != "1") return "Слишком много: " + result.ToString();
                if (гражданин_корреспондент_имя != "")
                {
                    sql = "SELECT isn_doc FROM " + localdb.sqlprefix + "DOC_RC WHERE (DOC_YEAR=" +
                    рег_дата.Split('-')[0] + ") AND (FREE_NUM_SEARCH='" +
                    рег_номер.ToUpper() + "') AND (DUE_DOCGROUP='" + группа_документов_due_sync + "')";
                    cmd = new OleDbCommand(sql, connection);
                    result = cmd.ExecuteScalar();
                    cmd.Dispose();
                    sql = "select count(*) from CITIZEN CIT inner join REF_LETTER REF on (CIT.ISN_CITIZEN=REF.ISN_CITIZEN) " +
                        "WHERE (REF.ISN_DOC_INP=" + result.ToString() + ") AND (CIT.CITIZEN_SURNAME='" +
                        гражданин_корреспондент_имя + "') AND (CITIZEN_ADDR like '%" + гражданин_корреспондент_адрес + "%')";
                    cmd = new OleDbCommand(sql, connection);
                    result = cmd.ExecuteScalar();
                    cmd.Dispose();
                    if ((result != null) && (result != DBNull.Value))
                    {
                        if (result.ToString() == "0")
                        {
                            return "Ошибка в гражданине - корреспонденте";
                        }
                        if (result.ToString() != "1") return "Слишком граждан: " + result.ToString();
                    }
                }
            }
            return "";
        }

        public static void CorrectДВО(OleDbConnection connection)
        {
            string sql = "SELECT isn_doc,FREE_NUM FROM " + localdb.sqlprefix + "DOC_RC WHERE (free_num like '%-0%') and (due_docgroup like '0.38WER.38XJW.%') and (doc_year < 2013) ";
            OleDbCommand cmd1 = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd1.ExecuteReader();
            string sql2 = "";
            string freenum = "";
            string rfreenum = "";
            while (dr.Read())
            {
                freenum = dr.GetString(1);
                //rfreenum = freenum.Split('/')[1] + "-" +freenum.Split('/')[0];
                rfreenum = freenum.Split('-')[0] + "-" + Int32.Parse(freenum.Split('-')[1]).ToString() + "-" + freenum.Split('-')[2];
                sql2 = " UPDATE " + localdb.sqlprefix + "DOC_RC SET FREE_NUM='" + rfreenum +
                    "', FREE_NUM_SEARCH='" + rfreenum.ToUpper() + "' WHERE ISN_DOC=" + dr[0].ToString() + "";
                System.IO.File.AppendAllText(@"D:\tmp\oda-export\test2.txt", sql2);
                OleDbCommand cmd2 = new OleDbCommand(sql2, connection);
                cmd2.ExecuteNonQuery();
                cmd2.Dispose();
            }

        }

        public static void CheckFile(string Folder, OleDbConnection connection, EApi.Head ApiConnection)
        {
            System.Windows.Forms.ListViewItem item;
            string группа_документов = "";
            string группа_документов_due = "";
            string группа_документов_due_sync = "";
            string рег_номер = "";
            string рег_дата = "";
            string гражданин_корреспондент_имя = "";
            string гражданин_корреспондент_адрес = "";
            string Error = "";
            ErrorFiles = 0;
            lsvCheckError.Items.Clear();
            List<string> files = new List<string>(File.ReadAllLines(Folder + "\\Check.log"));
            files = ClearFilesFromLog(files);
            CountFiles = files.Count;
            lsvCheckError.BeginUpdate();
            for (int progress = 0; progress < files.Count; progress += 1)
            {
                System.IO.FileInfo fi = new System.IO.FileInfo(files[progress]);
                //File.Copy(files[progress], Folder + "\\1\\"+fi.Name);
                XmlTextReader doc = new XmlTextReader(files[progress]);
                //System.Windows.Forms.Application.DoEvents();
                CurrentRows(progress / 4);
                try
                {
                    doc.ReadToFollowing("DOCGROUP");
                    doc.MoveToContent();
                    группа_документов_due = doc.GetAttribute("DUE");
                    группа_документов_due_sync = localdb.getDueDocgroup(группа_документов_due);
                    doc.ReadToDescendant("CLASSIF_NAME");
                    группа_документов = doc.ReadString();
                    doc.ReadToFollowing("FREE_NUM");
                    рег_номер = doc.ReadString();
                    doc.ReadToFollowing("DOC_DATE");
                    рег_дата = doc.ReadString();
                    //if (doc.ReadToFollowing("REF_LETTERS"))
                    //{
                    //    doc.ReadToDescendant("CITIZEN_SURNAME");
                    //    гражданин_корреспондент_имя = doc.ReadString();
                    //    doc.ReadToDescendant("CITIZEN_ADDR");
                    //    гражданин_корреспондент_адрес = doc.ReadString();
                    //}
                    //Error = CheckDocumentApi(ApiConnection, рег_дата, рег_номер, группа_документов_due_sync);
                    Error = CheckDocumentDb(connection, fi.Name,
                        рег_дата, рег_номер, группа_документов_due_sync,
                        гражданин_корреспондент_имя, гражданин_корреспондент_адрес);
                    if (Error != "")
                    {
                        item = lsvCheckError.Items.Add(files[progress]);
                        item.SubItems.Add(группа_документов);
                        item.SubItems.Add(рег_номер);
                        item.SubItems.Add(рег_дата);
                        item.SubItems.Add(Error);
                        ErrorFiles++;
                    }

                }
                catch (Exception ex)
                {
                    item = lsvCheckError.Items.Add(files[progress]);
                    item.SubItems.Add(группа_документов);
                    item.SubItems.Add(рег_номер);
                    item.SubItems.Add(рег_дата);
                    item.SubItems.Add(ex.Message);
                    ErrorFiles++;
                }
                группа_документов_due = "";
                группа_документов = "";
                рег_номер = "";
                рег_дата = "";
                гражданин_корреспондент_имя = "";
                гражданин_корреспондент_адрес = "";
            }
            lsvCheckError.EndUpdate();
        }

        public static void CorrectResolution(string Folder, string fi, OleDbConnection connection, string рег_дата,
            string рег_номер, string группа_документов_due_sync, string дата_резолюции,
            string текст_резолюции, string автор_резолюции, string дата_резолюции_плановая,
            string дата_резолюции_фактическая, string ответсвенный_исполнитель, string исполнители)
        {
            Int32 old_isn = Int32.Parse(fi.Split('.')[0]);
            string sql = "SELECT isn_doc FROM " + localdb.sqlprefix + "DOC_RC WHERE (DOC_YEAR=" +
            рег_дата.Split(' ')[0].Split('.')[2] + ") AND (FREE_NUM_SEARCH='" +
            рег_номер.ToUpper() + "') AND (DUE_DOCGROUP='" + группа_документов_due_sync + "')";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            object isn_doc = cmd.ExecuteScalar();
            cmd.Dispose();
            localdb.setNewIsn("DOC-CHECK", old_isn, Int32.Parse(isn_doc.ToString()));
            sql = "select RES.ISN_RESOLUTION, DEP.CLASSIF_NAME, RES.RESOLUTION_DATE, RES.PLAN_DATE, RES.FACT_DATE, RES.RESOLUTION_TEXT " +
                "FROM " + localdb.sqlprefix + "RESOLUTION RES INNER JOIN " + localdb.sqlprefix + "DEPARTMENT DEP ON (RES.DUE=DEP.DUE) " +
                "WHERE (RES.ISN_REF_DOC = " + isn_doc.ToString() + ")";
            cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            OleDbCommand cmd2;
            OleDbDataReader dr2;
            string executor, main_executor, main_executor_isn, main_executor_new;
            Int32 isn_reply;
            OleDbTransaction trans;
            while (dr.Read())
            {
                // Проверка на внесения вручную ответсвенного исполнителя
                sql = "SELECT * FROM PROT WHERE (OPER_DESCRIBE = 'OTV') AND (TABLE_ID = 'R')" +
                " AND (OPER_ID = 'U') AND (SUBOPER_ID = 'U') AND (REF_ISN=" + dr[0].ToString() + ")";
                cmd2 = new OleDbCommand(sql, connection);
                dr2 = cmd2.ExecuteReader();
                if (dr2.Read())
                {
                    dr2.Close();
                    continue;
                }
                dr2.Close();
                string rez_date = "";
                string rez_date_plan = "";
                string rez_date_fact = "";
                if (!dr.IsDBNull(2)) rez_date = dr.GetDateTime(2).ToString().Substring(0,10);
                if (!dr.IsDBNull(3)) rez_date_plan = dr.GetDateTime(3).ToString().Substring(0, 10);
                if (!dr.IsDBNull(4)) rez_date_fact = dr.GetDateTime(4).ToString().Substring(0, 10);

                if ((!dr.IsDBNull(1)) && (dr.GetString(1) != автор_резолюции)) continue;
                if ((dr.IsDBNull(1)) && (автор_резолюции != "")) continue;
                if ((dr.IsDBNull(2)) && (дата_резолюции != "")) continue;
                if ((!dr.IsDBNull(2)) && (rez_date != дата_резолюции.ToString().Substring(0, 10))) continue;
                if ((dr.IsDBNull(3)) && (дата_резолюции_плановая != "")) continue;
                if ((!dr.IsDBNull(3)) && (rez_date_plan != дата_резолюции_плановая.ToString().Substring(0, 10))) continue;
                if ((dr.IsDBNull(4)) && (дата_резолюции_фактическая != "")) continue;
                if ((!dr.IsDBNull(4)) && (rez_date_fact != дата_резолюции_фактическая.ToString().Substring(0, 10))) continue;
                if ((dr.IsDBNull(5)) && (текст_резолюции != "")) continue;
                if ((!dr.IsDBNull(5)) && (!CompareText(dr.GetString(5), текст_резолюции))) continue;

                sql = "select DEP.SURNAME, MAIN_FLAG, ISN_REPLY FROM " + localdb.sqlprefix + "DEPARTMENT DEP INNER JOIN " +
                    localdb.sqlprefix + "REPLY REP ON (REP.DUE=DEP.DUE) WHERE ISN_RESOLUTION=" + dr[0].ToString();
                cmd2 = new OleDbCommand(sql, connection);
                dr2 = cmd2.ExecuteReader();
                executor = "";
                main_executor = "";
                main_executor_isn = "";
                isn_reply = 0;
                while (dr2.Read())
                {
                    if (executor == "")
                    {
                        executor = dr2.GetString(0);
                    }
                    else
                    {
                        executor += ";" + dr2.GetString(0);
                    }
                    if (dr2.GetValue(1).ToString() == "1")
                    {
                        main_executor_isn = dr2.GetValue(2).ToString();
                        main_executor = dr2.GetString(0);
                        isn_reply = Int32.Parse(dr2.GetValue(2).ToString());
                        if (main_executor == ответсвенный_исполнитель) break;

                    }
                    if ((dr2.GetValue(1).ToString() != "1") && (CompareText(((dr2.GetString(0)).ToString().ToUpper()).Replace("Є", "Е"), (ответсвенный_исполнитель.ToString().ToUpper()).Replace("Є", "Е")) == true))
                    {
                        isn_reply = Int32.Parse(dr2.GetValue(2).ToString());

                    }

                }
                dr2.Close();
                string isident = (CompareText((executor.ToString().ToUpper()).Replace("Є", "Е"), (исполнители.ToString().ToUpper()).Replace("Є", "Е"))).ToString();
                if (CompareText((executor.ToString().ToUpper()).Replace("Є", "Е"), (исполнители.ToString().ToUpper()).Replace("Є", "Е")) != true) continue;
                trans = connection.BeginTransaction();
                try
                {
                    if (isn_reply != 0)
                    {
                        sql = "UPDATE REPLY SET MAIN_FLAG=0 WHERE ISN_RESOLUTION=" + dr[0].ToString();
                        cmd2 = new OleDbCommand(sql, connection, trans);
                        cmd2.ExecuteNonQuery();
                        sql = "UPDATE REPLY SET MAIN_FLAG=1 WHERE ISN_REPLY=" + isn_reply.ToString();
                        cmd2 = new OleDbCommand(sql, connection, trans);
                        cmd2.ExecuteNonQuery();
                        trans.Commit();
                    }
                }
                catch
                {
                    trans.Rollback();
                }
            }
            dr.Close();

        }

        private static bool CompareText(string text1, string text2)
        {
            string _text1 = "", _text2 = "";
            for (int i = 0; i < text1.Length; i++)
            {
                if ((text1[i] >= 'a') && (text1[i] <= 'z') ||
                    (text1[i] >= 'A') && (text1[i] <= 'Z') ||
                    (text1[i] >= 'а') && (text1[i] <= 'я') ||
                    (text1[i] >= 'А') && (text1[i] <= 'Я'))
                {
                    _text1 += text1[i];
                }
            }
            for (int i = 0; i < text2.Length; i++)
            {
                if ((text2[i] >= 'a') && (text2[i] <= 'z') ||
                    (text2[i] >= 'A') && (text2[i] <= 'Z') ||
                    (text2[i] >= 'а') && (text2[i] <= 'я') ||
                    (text2[i] >= 'А') && (text2[i] <= 'Я'))
                {
                    _text2 += text2[i];
                }
            }
            return (_text1 == _text2);
        }

        public static void JoinDepartments(OleDbConnection connection, Int32 FixIsn, string FixDue, List<Int32> OtherIsn, List<string> OtherDue)
        {
            if (OtherDue.Count == 0) return;
            string sql = "";
            OleDbCommand cmd;
            OleDbTransaction trans = connection.BeginTransaction();
            try
            {
                sql = "UPDATE " + localdb.sqlprefix + "DOC_RC SET DUE_PERSON_EXE='" + FixDue.ToString() + "' WHERE ";
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql += " (DUE_PERSON_EXE='" + OtherDue[i] + "') OR";
                }
                sql = sql.Substring(0, sql.Length - 2);
                cmd = new OleDbCommand(sql, connection, trans);
                cmd.ExecuteNonQuery();

                // Поле Кому
                sql = "UPDATE " + localdb.sqlprefix + "DOC_WHO SET DUE_PERSON='" + FixDue.ToString() + "' WHERE ";
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql += " (DUE_PERSON='" + OtherDue[i] + "') OR";
                }
                sql = sql.Substring(0, sql.Length - 2);
                cmd = new OleDbCommand(sql, connection, trans);
                cmd.ExecuteNonQuery();

                // Отключение триггеров на резолюциях
                if (localdb.sqlprefix == "")
                {
                    sql = "ALTER TABLE RESOLUTION DISABLE TRIGGER TR_IUD_RESOLUTION";
                    cmd = new OleDbCommand(sql, connection, trans);
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    sql = "";
                }

                // Авторы резолюций
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql = "UPDATE " + localdb.sqlprefix + "RESOLUTION SET DUE='" +
                        FixDue.ToString() + "' WHERE (DUE='" + OtherDue[i] + "')";
                    cmd = new OleDbCommand(sql, connection, trans);
                    cmd.ExecuteNonQuery();
                }

                // Контролеры
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql = "UPDATE " + localdb.sqlprefix + "RESOLUTION SET DUE_CONTROLLER='" +
                        FixDue.ToString() + "' WHERE (DUE_CONTROLLER='" + OtherDue[i] + "')";
                    cmd = new OleDbCommand(sql, connection, trans);
                    cmd.ExecuteNonQuery();
                }

                // Включение триггеров на резолюциях
                if (localdb.sqlprefix == "")
                {
                    sql = "ALTER TABLE RESOLUTION ENABLE TRIGGER TR_IUD_RESOLUTION";
                    cmd = new OleDbCommand(sql, connection, trans);
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    sql = "";
                }

                // Исполнители резолюций
                sql = "UPDATE " + localdb.sqlprefix + "REPLY SET DUE='" + FixDue.ToString() + "' WHERE ";
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql += " (DUE='" + OtherDue[i] + "') OR";
                }
                sql = sql.Substring(0, sql.Length - 2);
                cmd = new OleDbCommand(sql, connection, trans);
                cmd.ExecuteNonQuery();

                // Журнал передачи
                sql = "UPDATE " + localdb.sqlprefix + "JOURNAL SET DUE_ADRESAT='" + FixDue.ToString() + "' WHERE ";
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql += " (DUE_ADRESAT='" + OtherDue[i] + "') OR";
                }
                sql = sql.Substring(0, sql.Length - 2);
                cmd = new OleDbCommand(sql, connection, trans);
                cmd.ExecuteNonQuery();

                // Журнал пересылок
                sql = "UPDATE " + localdb.sqlprefix + "FORWARD SET DUE_ADRESAT='" + FixDue.ToString() + "' WHERE ";
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql += " (DUE_ADRESAT='" + OtherDue[i] + "') OR";
                }
                sql = sql.Substring(0, sql.Length - 2);
                cmd = new OleDbCommand(sql, connection, trans);
                cmd.ExecuteNonQuery();

                // Адресаты
                sql = "UPDATE " + localdb.sqlprefix + "REF_SEND SET DUE_DEP='" + FixDue.ToString() + "' WHERE ";
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql += " (DUE_DEP='" + OtherDue[i] + "') OR";
                }
                sql = sql.Substring(0, sql.Length - 2);
                cmd = new OleDbCommand(sql, connection, trans);
                cmd.ExecuteNonQuery();

                // Подписанты 
                sql = "UPDATE " + localdb.sqlprefix + "DOC_SIGN SET DUE_PERSON='" + FixDue.ToString() + "' WHERE ";
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql += " (DUE_PERSON='" + OtherDue[i] + "') OR";
                }
                sql = sql.Substring(0, sql.Length - 2);
                cmd = new OleDbCommand(sql, connection, trans);
                cmd.ExecuteNonQuery();

                // Визирующие
                sql = "UPDATE " + localdb.sqlprefix + "REF_VISA SET DUE_PERSON='" + FixDue.ToString() + "' WHERE ";
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql += " (DUE_PERSON='" + OtherDue[i] + "') OR";
                }
                sql = sql.Substring(0, sql.Length - 2);
                cmd = new OleDbCommand(sql, connection, trans);
                cmd.ExecuteNonQuery();

                // Исполнители документов
                sql = "UPDATE " + localdb.sqlprefix + "DOC_EXE SET DUE_PERSON='" + FixDue.ToString() + "' WHERE ";
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql += " (DUE_PERSON='" + OtherDue[i] + "') OR";
                }
                sql = sql.Substring(0, sql.Length - 2);
                cmd = new OleDbCommand(sql, connection, trans);
                cmd.ExecuteNonQuery();

                // Ассоциация пользователей 
                sql = "UPDATE " + localdb.sqlprefix + "USER_CL SET DUE_DEP='" + FixDue.ToString() + "' WHERE ";
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql += " (DUE_DEP='" + OtherDue[i] + "') OR";
                }
                sql = sql.Substring(0, sql.Length - 2);
                cmd = new OleDbCommand(sql, connection, trans);
                cmd.ExecuteNonQuery();

                // Права пользователей(резолюции)
                //sql = "UPDATE " + localdb.sqlprefix + "USERDEP SET DUE='" + FixDue.ToString() + "' WHERE ";
                //for (int i = 0; i < OtherDue.Count; i++)
                //{
                //    sql += " (DUE='" + OtherDue[i] + "') OR";
                //}
                //sql = sql.Substring(0, sql.Length - 2);
                //cmd = new OleDbCommand(sql, connection, trans);
                //cmd.ExecuteNonQuery();
                ////Удаляются дубликаты
                //sql = "SELECT ISN_LCLASSIF FROM" + localdb.sqlprefix + "USERDEP WHERE (DUE='" + FixDue.ToString() + "')";
                //cmd = new OleDbCommand(sql, connection, trans);
                //OleDbDataReader dr = cmd.ExecuteReader();
                //if (dr.Read())
                //{
                //    while (dr.Read())
                //    {
                //        sql = "DELETE "+ localdb.sqlprefix + "USERDEP WHERE ISN_LCLASSIF="+dr.GetValue(0).ToString();
                //        OleDbCommand cmd2 = new OleDbCommand(sql, connection, trans);
                //        cmd2.ExecuteNonQuery();
                //        cmd2.Dispose();

                //    }
                //}
                //dr.Close();

                // Кабинеты 

                // Картотеки

                // Удаление "старых"
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    if (OtherDue[i] == FixDue) continue;
                    try
                    {
                        sql = "DELETE " + localdb.sqlprefix + "DEPARTMENT WHERE (DUE like'" +
                            OtherDue[i] + "%')";
                        cmd = new OleDbCommand(sql, connection, trans);
                        cmd.ExecuteNonQuery();
                    }
                    catch // Если удалить не удалось проставляем признак логического удаления
                    {
                        sql = "UPDATE " + localdb.sqlprefix + "DEPARTMENT SET DELETED=1 WHERE (DUE like'" +
                            OtherDue[i] + "%')";
                        cmd = new OleDbCommand(sql, connection, trans);
                        cmd.ExecuteNonQuery();
                    }
                }

                // LocalDB
                sql = "UPDATE DEPARTMENT SET IMPORTISN=" + FixIsn.ToString() + ", IMPORTDUE='" + FixDue + "' WHERE ";
                for (int i = 0; i < OtherDue.Count; i++)
                {
                    sql += " (IMPORTDUE='" + OtherDue[i] + "') OR";
                }
                sql = sql.Substring(0, sql.Length - 2);
                cmd = new OleDbCommand(sql, localdb.Connection);
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }

        public static List<string> ViewCardFromDepartment(OleDbConnection connection, string Due)
        {
            CountFiles = 6;
            string sql = "";
            List<string> result = new List<string>();
            #region Старый универсальный запрос
            //sql = "select DISTINCT crd.CARD_NAME from " + localdb.sqlprefix + "department crd, " +
            //    "(select DISTINCT rc.ISN_DOC DOC_ISN, rc.ISN_CARD_REG CARD_ISN, " +
            //    "frwd.DUE_CORRESP FCORRESP, jrn.DUE_CORRESP JCORRESP from " + localdb.sqlprefix + "DOC_RC rc " +
            //    "left join " + localdb.sqlprefix + "REF_SEND rs on (rc.ISN_DOC=rs.ISN_REF_DOC) " +
            //    "left join " + localdb.sqlprefix + "DOC_WHO dw on (rc.ISN_DOC=dw.ISN_DOC) " +
            //    "left join " + localdb.sqlprefix + "DOC_SIGN ds on (rc.ISN_DOC=ds.ISN_DOC) " +
            //    "left join " + localdb.sqlprefix + "DOC_EXE de on (rc.ISN_DOC=de.ISN_DOC) " +
            //    "left join " + localdb.sqlprefix + "REF_VISA rv on (rc.ISN_DOC=rv.ISN_DOC_OUT) " +
            //    "left join " + localdb.sqlprefix + "FORWARD frwd on (rc.ISN_DOC=frwd.ISN_DOC) " +
            //    "left join " + localdb.sqlprefix + "JOURNAL jrn on (rc.ISN_DOC=jrn.ISN_DOC) " +
            //    "where (rc.DUE_PERSON_EXE='"+Due+"') or  " +
            //    "(rs.DUE_DEP='" + Due + "') or  " +
            //    "(dw.DUE_PERSON='" + Due + "') or  " +
            //    "(ds.DUE_PERSON='" + Due + "') or  " +
            //    "(de.DUE_PERSON='" + Due + "') or  " +
            //    "(rv.DUE_PERSON='" + Due + "') or " +
            //    "(frwd.DUE_ADRESAT='" + Due + "') or " +
            //    "(jrn.DUE_ADRESAT='" + Due + "')) doc, " +
            //    "" + localdb.sqlprefix + "REF_ACCESS_CARD rac, " +
            //    "(select res.ISN_RESOLUTION RESOLUTION_ISN from " + localdb.sqlprefix + "RESOLUTION res " +
            //    "left join " + localdb.sqlprefix + "REPLY rep on (res.ISN_RESOLUTION=rep.ISN_RESOLUTION) " +
            //    "WHERE " +
            //    "(res.DUE = '" + Due + "') or " +
            //    "(res.DUE_CONTROLLER = '" + Due + "') or  " +
            //    "(rep.DUE = '" + Due + "') " +
            //    ") resol, " + localdb.sqlprefix + "RESOLUTION_CARD resc " +
            //    "where (crd.CARD_flag=1) and (" +
            //    "(doc.CARD_ISN = crd.ISN_NODE) or  " +
            //    "(rac.ISN_REF_DOC=doc.DOC_ISN) or " +
            //    "(rac.ISN_RESOLUTION=resol.RESOLUTION_ISN) or " +
            //    "(resc.ISN_RESOLUTION=resol.RESOLUTION_ISN) or  " +
            //    "(doc.FCORRESP=crd.due) or  " +
            //    "(doc.JCORRESP=crd.due))";
            #endregion Старый универсальный запрос
            // Картотеки
            sql = "select DISTINCT crd.CARD_NAME " +
                "from " + localdb.sqlprefix + "DOC_RC rc " +
                "left join " + localdb.sqlprefix + "REF_SEND rs on (rc.ISN_DOC=rs.ISN_REF_DOC) " +
                "left join " + localdb.sqlprefix + "DOC_WHO dw on (rc.ISN_DOC=dw.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "DOC_SIGN ds on (rc.ISN_DOC=ds.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "DOC_EXE de on (rc.ISN_DOC=de.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "REF_VISA rv on (rc.ISN_DOC=rv.ISN_DOC_OUT) " +
                "left join " + localdb.sqlprefix + "FORWARD frwd on (rc.ISN_DOC=frwd.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "JOURNAL jrn on (rc.ISN_DOC=jrn.ISN_DOC) " +
                "inner join " + localdb.sqlprefix + "department crd on " +
                "((rc.ISN_CARD_REG=crd.ISN_NODE) or (frwd.DUE_CORRESP=crd.DUE) or (jrn.DUE_CORRESP=crd.DUE)) " +
                "where (crd.CARD_FLAG=1) and (" +
                "(rc.DUE_PERSON_EXE='" + Due + "') or  " +
                "(rs.DUE_DEP='" + Due + "') or " +
                "(dw.DUE_PERSON='" + Due + "') or " +
                "(ds.DUE_PERSON='" + Due + "') or " +
                "(de.DUE_PERSON='" + Due + "') or " +
                "(rv.DUE_PERSON='" + Due + "') or " +
                "(frwd.DUE_ADRESAT='" + Due + "') or " +
                "(jrn.DUE_ADRESAT='" + Due + "'))";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (!dr.IsDBNull(0)) result.Add("Картотека: " + dr.GetString(0));
            }
            dr.Close();
            CurrentRows(1);
            sql = "select DISTINCT crd.CARD_NAME " +
                "from " + localdb.sqlprefix + "DOC_RC rc " +
                "left join " + localdb.sqlprefix + "REF_SEND rs on (rc.ISN_DOC=rs.ISN_REF_DOC) " +
                "left join " + localdb.sqlprefix + "DOC_WHO dw on (rc.ISN_DOC=dw.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "DOC_SIGN ds on (rc.ISN_DOC=ds.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "DOC_EXE de on (rc.ISN_DOC=de.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "REF_VISA rv on (rc.ISN_DOC=rv.ISN_DOC_OUT) " +
                "left join " + localdb.sqlprefix + "FORWARD frwd on (rc.ISN_DOC=frwd.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "JOURNAL jrn on (rc.ISN_DOC=jrn.ISN_DOC) " +
                "inner join " + localdb.sqlprefix + "REF_ACCESS_CARD rac on (rac.ISN_REF_DOC=rc.ISN_DOC)" +
                "inner join " + localdb.sqlprefix + "department crd on (crd.DUE=rac.DUE) " +
                "where (crd.CARD_FLAG=1) and (" +
                "(rc.DUE_PERSON_EXE='" + Due + "') or  " +
                "(rs.DUE_DEP='" + Due + "') or " +
                "(dw.DUE_PERSON='" + Due + "') or " +
                "(ds.DUE_PERSON='" + Due + "') or " +
                "(de.DUE_PERSON='" + Due + "') or " +
                "(rv.DUE_PERSON='" + Due + "') or " +
                "(frwd.DUE_ADRESAT='" + Due + "') or " +
                "(jrn.DUE_ADRESAT='" + Due + "'))";
            cmd = new OleDbCommand(sql, connection);
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (!dr.IsDBNull(0) && (!result.Contains("Картотека: " + dr.GetString(0)))) result.Add("Картотека: " + dr.GetString(0));
            }
            dr.Close();
            CurrentRows(2);
            sql = "select crd.CARD_NAME from " + localdb.sqlprefix + "RESOLUTION res " +
                "left join " + localdb.sqlprefix + "REPLY rep on (res.ISN_RESOLUTION=rep.ISN_RESOLUTION) " +
                "inner join " + localdb.sqlprefix + "RESOLUTION_CARD resc on (res.ISN_RESOLUTION=resc.ISN_RESOLUTION) " +
                "inner join " + localdb.sqlprefix + "department crd on (crd.DUE=resc.DUE) " +
                "WHERE (crd.CARD_FLAG=1) and (" +
                "(res.DUE = '" + Due + "') or " +
                "(res.DUE_CONTROLLER = '" + Due + "') or  " +
                "(rep.DUE = '" + Due + "'))";
            cmd = new OleDbCommand(sql, connection);
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (!dr.IsDBNull(0) && (!result.Contains("Картотека: " + dr.GetString(0)))) result.Add("Картотека: " + dr.GetString(0));
            }
            dr.Close();
            CurrentRows(3);
            sql = "select crd.CARD_NAME from " + localdb.sqlprefix + "RESOLUTION res " +
                "left join " + localdb.sqlprefix + "REPLY rep on (res.ISN_RESOLUTION=rep.ISN_RESOLUTION) " +
                "inner join " + localdb.sqlprefix + "REF_ACCESS_CARD rac on " +
                "((res.ISN_RESOLUTION=rac.ISN_RESOLUTION) or (res.ISN_REF_DOC = rac.ISN_REF_DOC)) " +
                "inner join " + localdb.sqlprefix + "department crd on (crd.DUE=rac.DUE) " +
                "WHERE (crd.CARD_FLAG=1) and (" +
                "(res.DUE = '" + Due + "') or " +
                "(res.DUE_CONTROLLER = '" + Due + "') or  " +
                "(rep.DUE = '" + Due + "'))";
            cmd = new OleDbCommand(sql, connection);
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (!dr.IsDBNull(0) && (!result.Contains("Картотека: " + dr.GetString(0)))) result.Add("Картотека: " + dr.GetString(0));
            }
            dr.Close();
            CurrentRows(4);
            // Группы
            sql = "select DISTINCT grp.CLASSIF_NAME " +
                "from " + localdb.sqlprefix + "DOC_RC rc " +
                "left join " + localdb.sqlprefix + "REF_SEND rs on (rc.ISN_DOC=rs.ISN_REF_DOC) " +
                "left join " + localdb.sqlprefix + "DOC_WHO dw on (rc.ISN_DOC=dw.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "DOC_SIGN ds on (rc.ISN_DOC=ds.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "DOC_EXE de on (rc.ISN_DOC=de.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "REF_VISA rv on (rc.ISN_DOC=rv.ISN_DOC_OUT) " +
                "left join " + localdb.sqlprefix + "FORWARD frwd on (rc.ISN_DOC=frwd.ISN_DOC) " +
                "left join " + localdb.sqlprefix + "JOURNAL jrn on (rc.ISN_DOC=jrn.ISN_DOC) " +
                "inner join " + localdb.sqlprefix + "DOCGROUP_CL grp on (grp.DUE=rc.DUE_DOCGROUP) " +
                "where " +
                "(rc.DUE_PERSON_EXE='" + Due + "') or  " +
                "(rs.DUE_DEP='" + Due + "') or " +
                "(dw.DUE_PERSON='" + Due + "') or " +
                "(ds.DUE_PERSON='" + Due + "') or " +
                "(de.DUE_PERSON='" + Due + "') or " +
                "(rv.DUE_PERSON='" + Due + "') or " +
                "(frwd.DUE_ADRESAT='" + Due + "') or " +
                "(jrn.DUE_ADRESAT='" + Due + "')";
            cmd = new OleDbCommand(sql, connection);
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (!dr.IsDBNull(0) && (!result.Contains("Группа: " + dr.GetString(0)))) result.Add("Группа: " + dr.GetString(0));
            }
            dr.Close();
            CurrentRows(5);
            sql = "select grp.CLASSIF_NAME from " + localdb.sqlprefix + "RESOLUTION res " +
                "inner join " + localdb.sqlprefix + "REPLY rep on (res.ISN_RESOLUTION=rep.ISN_RESOLUTION) " +
                "inner join " + localdb.sqlprefix + "DOC_RC rc on (res.ISN_REF_DOC = rc.ISN_DOC) " +
                "inner join " + localdb.sqlprefix + "DOCGROUP_CL grp on (grp.DUE=rc.DUE_DOCGROUP) " +
                "WHERE " +
                "(res.DUE = '" + Due + "') or " +
                "(res.DUE_CONTROLLER = '" + Due + "') or  " +
                "(rep.DUE = '" + Due + "')";
            cmd = new OleDbCommand(sql, connection);
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (!dr.IsDBNull(0) && (!result.Contains("Группа: " + dr.GetString(0)))) result.Add("Группа: " + dr.GetString(0));
            }
            dr.Close();
            CurrentRows(6);
            return result;
        }

        private static List<string> ClearFilesFromLog(List<string> files)
        {
            int first, last;
            string s;
            List<string> result = new List<string>();
            for (int i = 0; i < files.Count; i++)
            {
                first = files[i].IndexOf(":\\") - 1;
                if (first < 0) continue;
                last = files[i].IndexOf(".xml") + 4;
                s = files[i].Substring(first, last - first);
                result.Add(s);
            }
            return result;
        }

        public static void IsnFactory(OleDbConnection connection, String Folder)
        {
            //string группа_документов = "";
            string группа_документов_due = "";
            string группа_документов_due_sync = "";
            string рег_номер = "";
            string рег_дата = "";
            DateTime doc_date;

            //OleDbTransaction trans = localdb.Connection.BeginTransaction();
            string sql = "DELETE FROM PROTOCOL WHERE TNAME='RC'";
            OleDbCommand cmd = new OleDbCommand(sql, localdb.Connection);
            cmd.ExecuteNonQuery();

            object result;
            List<string> files = new List<string>(System.IO.Directory.EnumerateFiles(Folder, "*.xml"));
            CountFiles = files.Count;

            for (int progress = 0; progress < files.Count; progress++)
            {
                System.IO.FileInfo fi = new System.IO.FileInfo(files[progress]);
                XmlTextReader doc = new XmlTextReader(files[progress]);
                System.Windows.Forms.Application.DoEvents();
                CurrentRows(progress);
                try
                {
                    doc.ReadToFollowing("DOCGROUP");
                    doc.MoveToContent();
                    группа_документов_due = doc.GetAttribute("DUE");
                    группа_документов_due_sync = localdb.getDueDocgroup(группа_документов_due);
                    //doc.ReadToDescendant("CLASSIF_NAME");
                    //группа_документов = doc.ReadString();
                    doc.ReadToFollowing("FREE_NUM");
                    рег_номер = doc.ReadString();
                    doc.ReadToFollowing("DOC_DATE");
                    рег_дата = doc.ReadString();

                    doc_date = DateTime.Parse(рег_дата);
                    sql = "SELECT ISN_DOC FROM " + localdb.sqlprefix + "DOC_RC WHERE (DOC_YEAR=" +
                        doc_date.Year.ToString() + ") AND (FREE_NUM_SEARCH='" +
                        рег_номер.ToUpper() + "') AND (DUE_DOCGROUP='" + группа_документов_due_sync + "')";
                    cmd = new OleDbCommand(sql, connection);
                    result = cmd.ExecuteScalar();
                    cmd.Dispose();
                    if ((result != null) && (result != DBNull.Value))
                    {
                        sql = "INSERT INTO PROTOCOL (TNAME, IMPORTTIME, IMPORTISN, IMPORTDUE, EXPORTISN) " +
                            "VALUES('RC','" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") +
                            "'," + result.ToString() + ",'" + doc_date.Year.ToString() + "'," +
                                fi.Name.Substring(0, fi.Name.Length - 4) + ")";
                        cmd = new OleDbCommand(sql, localdb.Connection);
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }
                }
                catch
                {

                }
            }
            //trans.Commit();
        }
        public static void LinksFactory(OleDbConnection connection, String Folder, EApi.Head ApiConnection)
        {
            string aisndoc = "";
            string aisn_linkdoc = "";
            string aisndoc_sync = "";
            string aisn_linkdoc_sync = "";
            string alinked_num = "";
            string aisn_link = "";
            string aisn_link_sync = "";
            //string aisn_link_para = "";
            //string aisn_link_para_sync = "";


            //OleDbTransaction trans = localdb.Connection.BeginTransaction();
            string sql = "DELETE FROM PROTOCOL WHERE TNAME='LINK'";
            OleDbCommand cmd = new OleDbCommand(sql, localdb.Connection);
            cmd.ExecuteNonQuery();
            sql = "DELETE FROM PROTOCOL WHERE TNAME='REF_LINK'";
            cmd = new OleDbCommand(sql, localdb.Connection);
            cmd.ExecuteNonQuery();

            object result;
            List<string> files = new List<string>(System.IO.Directory.EnumerateFiles(Folder, "*.xml"));
            CountFiles = files.Count;
            XmlTextReader doc;
            for (int progress = 0; progress < files.Count; progress++)
            {
                System.IO.FileInfo fi = new System.IO.FileInfo(files[progress]);
                aisndoc = fi.Name.Substring(0, fi.Name.Length - 4);
                sql = "select importisn from protocol where (tname = 'RC') and (exportisn =" + aisndoc + " )";
                cmd = new OleDbCommand(sql, localdb.Connection);
                result = cmd.ExecuteScalar();
                if ((result != null) && (result != DBNull.Value))
                    aisndoc_sync = result.ToString();
                else
                {
                    doc = new XmlTextReader(files[progress]);
                    doc.ReadToFollowing("DOCGROUP");
                    doc.MoveToContent();
                    doc.ReadToDescendant("CLASSIF_NAME");
                    aisndoc_sync = "";
                    System.IO.File.AppendAllText(Folder + "\\linkload.log", "[" + fi.Name + "]||[" + doc.ReadString() + "] Нет соответсвия ISN-ов документов\r\n");
                    continue;
                }
                doc = new XmlTextReader(files[progress]);
                System.Windows.Forms.Application.DoEvents();
                CurrentRows(progress);
                try
                {
                    doc.ReadToFollowing("LINKS");
                    while (doc.ReadToDescendant("LINK"))
                    {
                        XmlReader link = doc.ReadSubtree();
                        bool exit_link = false;
                        while (link.Read() && (!exit_link))
                        {
                            alinked_num = "";
                            switch (link.Name)
                            {
                                case "LINKED_NUM":
                                    alinked_num = link.ReadElementString();
                                    break;
                                case "LNK":
                                    exit_link = true;
                                    break;
                            }
                        }
                        //if (link.ReadInnerXml().IndexOf("LINKED_NUM") == -1) System.Windows.Forms.MessageBox.Show(link.ReadInnerXml());
                        //if (link.ReadToFollowing("LINKED_NUM"))
                        //    alinked_num = link.ReadString();
                        //else alinked_num = "";


                        //link.ReadToFollowing("LNK");
                        if (link.ReadToFollowing("CLASSIF_NAME"))
                            aisn_link = link.ReadString();
                        else aisn_link = "";
                        sql = "select ISN_LCLASSIF from " + localdb.sqlprefix + "LINK_CL where (CLASSIF_NAME ='" + aisn_link + "' )";
                        cmd = new OleDbCommand(sql, localdb.SyncConnection);
                        result = cmd.ExecuteScalar();
                        if ((result != null) && (result != DBNull.Value))
                            aisn_link_sync = result.ToString();
                        else aisn_link_sync = "";


                        //if (link.ReadToFollowing("CLASSIF_NAME"))
                        //    aisn_link_para = link.ReadString();
                        //else aisn_link_para = "";

                        link.ReadToFollowing("RC");
                        XmlReader rc = link.ReadSubtree();
                        if (rc.ReadToFollowing("ISN_DOC"))
                        {
                            aisn_linkdoc = rc.ReadString();
                            sql = "select importisn from protocol where (tname = 'RC') and (exportisn =" + aisn_linkdoc + " )";
                            cmd = new OleDbCommand(sql, localdb.Connection);
                            result = cmd.ExecuteScalar();
                            if ((result != null) && (result != DBNull.Value))
                                aisn_linkdoc_sync = result.ToString();
                            else aisn_linkdoc_sync = "";

                        }
                        else aisn_linkdoc = "";

                        if ((aisndoc != "") && (aisn_link != ""))
                        {
                            if (aisn_linkdoc_sync != "")
                            {
                                sql = "select ISN_CLLINK from " + localdb.sqlprefix + "REF_LINK where (ISN_REF_DOC =" + aisndoc_sync + " )"
                                    + " or (ISN_LINKED_DOC =" + aisndoc_sync + " )"
                                    + " or (ISN_REF_DOC =" + aisn_linkdoc_sync + " )"
                                    + " or (ISN_LINKED_DOC =" + aisn_linkdoc_sync + " )";
                                cmd = new OleDbCommand(sql, localdb.SyncConnection);
                                result = cmd.ExecuteScalar();
                                if ((result != null) && (result != DBNull.Value)) continue;
                                // выполняем добавление связки
                                try
                                {
                                    dynamic proc = ApiConnection.GetProc("add_link");
                                    proc.Parameters.Append(proc.CreateParameter("aisndoc", 3, 1, 4, float.Parse(aisndoc_sync)));
                                    proc.Parameters.Append(proc.CreateParameter("aisn_linkdoc", 3, 1, 4, float.Parse(aisn_linkdoc_sync)));
                                    proc.Parameters.Append(proc.CreateParameter("aisn_link", 3, 1, 4, float.Parse(aisn_link_sync)));
                                    proc.Parameters.Append(proc.CreateParameter("alinked_num", 200, 1, 255, alinked_num));
                                    proc.Parameters.Append(proc.CreateParameter("acard", 200, 1, 48, DBNull.Value));
                                    proc.Parameters.Append(proc.CreateParameter("aUrlStr", 200, 1, 255, DBNull.Value));
                                    ApiConnection.ExecuteProc(proc);
                                    if (ApiConnection.ErrCode != 0) throw new Exception("SaveLINK [" + ApiConnection.ErrText + "]\n" + ApiConnection.ErrCode.ToString());
                                }
                                catch (Exception ex)
                                {
                                    //System.Windows.Forms.MessageBox.Show(ex.Message);
                                    System.IO.File.AppendAllText(Folder + "\\linkload.log", "[" + fi.Name + "]" + ex.Message + "\r\n");

                                }
                            }
                            else
                            {
                                // выполняем добавление связки

                                try
                                {
                                    dynamic proc = ApiConnection.GetProc("add_link");
                                    proc.Parameters.Append(proc.CreateParameter("aisndoc", 3, 1, 4, float.Parse(aisndoc_sync)));
                                    proc.Parameters.Append(proc.CreateParameter("aisn_linkdoc", 3, 1, 4, DBNull.Value));
                                    proc.Parameters.Append(proc.CreateParameter("aisn_link", 3, 1, 4, float.Parse(aisn_link_sync)));
                                    proc.Parameters.Append(proc.CreateParameter("alinked_num", 200, 1, 255, alinked_num));
                                    proc.Parameters.Append(proc.CreateParameter("acard", 200, 1, 48, DBNull.Value));
                                    proc.Parameters.Append(proc.CreateParameter("aUrlStr", 200, 1, 255, DBNull.Value));
                                    ApiConnection.ExecuteProc(proc);
                                    if (ApiConnection.ErrCode != 0) throw new Exception("SaveLINK [" + ApiConnection.ErrText + "]\n" + ApiConnection.ErrCode.ToString());
                                }
                                catch (Exception ex)
                                {
                                    //System.Windows.Forms.MessageBox.Show(ex.Message);
                                    System.IO.File.AppendAllText(Folder + "\\linkload.log", "[" + fi.Name + "]" + ex.Message + "\r\n");

                                }
                            }
                        }


                    }
                }
                catch (Exception ex)
                {
                    System.IO.File.AppendAllText(Folder + "\\linkload.log", "[" + fi.Name + "]" + ex.Message + "\r\n");
                }
            }
            //trans.Commit();
        }
        public static void CoverDocsFactory(OleDbConnection connection, String Folder, EApi.Head ApiConnection)
        {
            string aisndoc = "";
            string ISN_ORGANIZ = "";
            Int32 IMPORT_ISN = 0;
            string DUE_ORGANIZ = "";
            object result;
            localdb.analyz = TAnalys.byname;
            List<string> files = new List<string>(System.IO.Directory.EnumerateFiles(Folder, "*.xml"));
            CountFiles = files.Count;
            XmlDocument doc = new XmlDocument();
            for (int progress = 0; progress < files.Count; progress++)
            {
                System.IO.FileInfo fi = new System.IO.FileInfo(files[progress]);
                aisndoc = fi.Name.Substring(0, fi.Name.Length - 4);
                string sql = "select importisn from protocol where (tname = 'RC') and (exportisn =" + aisndoc + " )";
                OleDbCommand cmd = new OleDbCommand(sql, localdb.Connection);
                result = cmd.ExecuteScalar();
                if ((result != null) && (result != DBNull.Value))
                    IMPORT_ISN = Int32.Parse(result.ToString());
                else
                {
                    IMPORT_ISN = 0;
                    System.IO.File.AppendAllText(Folder + "\\CoverDocLoad.log", "[" + fi.Name + "]  Нет соответсвия ISN-ов документов\r\n");
                    continue;
                }

                doc.Load(files[progress]);
                //System.Windows.Forms.Application.DoEvents();
                CurrentRows(progress);
                XmlNode CoverCoresps = doc.SelectSingleNode("//rc/CORRESPS");
                if (CoverCoresps == null || ApiConnection == null || IMPORT_ISN == 0) return;
                XmlNodeList CoverCoresp = CoverCoresps.SelectNodes("CORRESP[CORRESP_KIND='2']");
                for (int i = 0; i < CoverCoresp.Count; i++)
                {
                    if (CoverCoresp[i].SelectSingleNode("DUE_ORGANIZ").InnerText == "") continue;
                    DUE_ORGANIZ = "";
                    ISN_ORGANIZ = "";
                    //sql = "select importisn, importdue from ORGANIZ_CL where ISN_NODE =" + CoverCoresp[i].SelectSingleNode("DUE_ORGANIZ/@ISN").InnerText;
                    //cmd = new OleDbCommand(sql, localdb.Connection);
                    //OleDbDataReader dr = cmd.ExecuteReader();
                    //if (dr.Read())
                    //{
                    //    DUE_ORGANIZ = dr.GetString(1);
                    //    ISN_ORGANIZ = dr.GetValue(0).ToString();
                    //}
                    //else
                    //{
                        DUE_ORGANIZ = localdb.syncOrganiz(CoverCoresp[i].SelectSingleNode("DUE_ORGANIZ"));
                        ISN_ORGANIZ = localdb.syncOrganizISN(CoverCoresp[i].SelectSingleNode("DUE_ORGANIZ")).ToString();
                    //    System.IO.File.AppendAllText(Folder + "\\CoverDocLoad.log", "[" + fi.Name + "]  Нет соответсвия ISN-ов организации(корреспондента)\r\n");
                    //    continue;
                    //}


                    DateTime? CORRESP_DATE = (CoverCoresp[i].SelectNodes("CORRESP_DATE").Count > 0)?(DateTime?)(DateTime.Parse(CoverCoresp[i].SelectSingleNode("CORRESP_DATE").InnerText)):null; 
                    
                string CORRESP_NUM = (CoverCoresp[i].SelectNodes("CORRESP_NUM").Count>0)?(CoverCoresp[i].SelectSingleNode("CORRESP_NUM").InnerText):"";

                    sql = "SELECT COUNT(*) FROM " + localdb.sqlprefix + "REF_CORRESP WHERE (CORRESP_DATE=:CORR_DATE) AND (CORRESP_NUM_SEARCH='" +
                                    CORRESP_NUM.ToUpper() + "') AND (DUE_ORGANIZ='" + DUE_ORGANIZ + "') AND (ISN_DOC_INP=" + IMPORT_ISN + ")";
                    cmd = new OleDbCommand(sql, connection);
                    cmd.Parameters.Add("CORR_DATE", OleDbType.Date);
                    cmd.Parameters["CORR_DATE"].Value = (CORRESP_DATE == null) ? DBNull.Value : (object)CORRESP_DATE;
                    cmd.Parameters["CORR_DATE"].Direction = ParameterDirection.Input;

                    object cnt = cmd.ExecuteScalar();
                    cmd.Dispose();
                    if (cnt.ToString() == "0")
                        {
                            try
                            {
                                dynamic proc = ApiConnection.GetProc("add_cover_doc");
                                proc.Parameters.Append(proc.CreateParameter("aisndoc", 3, 1, 4, (double)IMPORT_ISN));
                                proc.Parameters.Append(proc.CreateParameter("acard", 200, 1, 48, localdb.topCard));
                                proc.Parameters.Append(proc.CreateParameter("acab", 3, 1, 4, (float)0));
                                proc.Parameters.Append(proc.CreateParameter("acodes", 200, 1, 8000, DUE_ORGANIZ));
                                if (CoverCoresp[i].SelectNodes("CORRESP_NUM").Count > 0) proc.Parameters.Append(proc.CreateParameter("aCorrespNums", 200, 1, 8000, CoverCoresp[i].SelectSingleNode("CORRESP_NUM").InnerText));
                                else proc.Parameters.Append(proc.CreateParameter("aCorrespNums", 200, 1, 64, DBNull.Value));
                                if (CoverCoresp[i].SelectNodes("CORRESP_DATE").Count > 0) proc.Parameters.Append(proc.CreateParameter("aCorrespDates", 200, 1, 8000, localdb.convertDate(CoverCoresp[i].SelectSingleNode("CORRESP_DATE").InnerText)));
                                else proc.Parameters.Append(proc.CreateParameter("aCorrespDates", 200, 1, 20, DBNull.Value));
                                if (CoverCoresp[i].SelectNodes("CORRESP_SIGN").Count > 0) proc.Parameters.Append(proc.CreateParameter("aCorrespSigns", 200, 1, 8000, CoverCoresp[i].SelectSingleNode("CORRESP_SIGN").InnerText));
                                else proc.Parameters.Append(proc.CreateParameter("aCorrespSigns", 200, 1, 8000, DBNull.Value));
                                if (CoverCoresp[i].SelectNodes("ANNOTAT").Count > 0) proc.Parameters.Append(proc.CreateParameter("aAnnotats", 200, 1, 8000, CoverCoresp[i].SelectSingleNode("ANNOTAT").InnerText));
                                else proc.Parameters.Append(proc.CreateParameter("aAnnotats", 200, 1, 8000, DBNull.Value));
                                if (CoverCoresp[i].SelectNodes("CONSISTS").Count > 0) proc.Parameters.Append(proc.CreateParameter("aConsists", 200, 1, 8000, CoverCoresp[i].SelectSingleNode("CONSISTS").InnerText));
                                else proc.Parameters.Append(proc.CreateParameter("aConsists", 200, 1, 8000, DBNull.Value));
                                if (CoverCoresp[i].SelectNodes("NOTE").Count > 0) proc.Parameters.Append(proc.CreateParameter("aNotes", 200, 1, 8000, CoverCoresp[i].SelectSingleNode("NOTE").InnerText));
                                else proc.Parameters.Append(proc.CreateParameter("aNotes", 200, 1, 8000, DBNull.Value));

                                if (CoverCoresp[i].SelectNodes("ISN_CONTACT").Count > 0) proc.Parameters.Append(proc.CreateParameter("aIsnsContact", 200, 1, 8000, localdb.syncCoverContact(ISN_ORGANIZ, IMPORT_ISN)));
                                else proc.Parameters.Append(proc.CreateParameter("aIsnsContact", 200, 1, 8000, DBNull.Value));
                                ApiConnection.ExecuteProc(proc);
                                if (ApiConnection.ErrCode != 0) throw new Exception("SaveCoverDoc [" + ApiConnection.ErrText + "]\n" + ApiConnection.ErrCode.ToString());

                            }
                            catch (Exception ex)
                            {
                                System.IO.File.AppendAllText(Folder + "\\CoverDocLoad.log", "[" + fi.Name + "]" + ex.Message + "\r\n");
                            }
                        }
                        else continue;
                }
            }
        }
    }
}
